﻿--HEADER Add ExcessSigma and ExcessSigmaMove columns to ExcessTimeSerie

TRUNCATE TABLE TradingData.ExcessTimeSerie
GO

ALTER TABLE TradingData.ExcessTimeSerie
ADD ExcessSigma FLOAT NOT null
GO

ALTER TABLE TradingData.ExcessTimeSerie
ADD ExcessSigmaMove FLOAT NOT null
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [TradingData].[IT_ExcessTimeSerie] ON [TradingData].[ExcessTimeSerie] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON;

UPDATE T
SET 
    T.[Date]  =i.[Date],               
    T.[BenchmarkId]= i.[BenchmarkId],    
    T.[Sigma] = i.[Sigma],    
    T.[SigmaMove]=i.[SigmaMove],               
	T.[ExcessSigma] = i.[ExcessSigma],    
    T.[ExcessSigmaMove]=i.[ExcessSigmaMove],               
    T.[SecurityReturn]=i.[SecurityReturn],    
    T.[BenchmarkReturn]=i.[BenchmarkReturn],
    T.[PreviousUnitPosition]=i.[PreviousUnitPosition],
    T.[SecurityId]= i.[SecurityId]	
FROM  [TradingData].[ExcessTimeSerie] T JOIN inserted i
ON T.SecurityId = i.SecurityId and T.[Date] = i.[Date] and T.[BenchmarkId] = i.[BenchmarkId]



INSERT [TradingData].[ExcessTimeSerie](
    [Date],               
    [BenchmarkId],    
    [Sigma],    
    [SigmaMove],               
	[ExcessSigma],    
    [ExcessSigmaMove],               
    [SecurityReturn],    
    [BenchmarkReturn],
    [PreviousUnitPosition],
    [SecurityId]	)
SELECT	
	i.[Date],               
    i.[BenchmarkId],    
    i.[Sigma],    
    i.[SigmaMove],               
	i.[ExcessSigma],    
    i.[ExcessSigmaMove],               
    i.[SecurityReturn],    
    i.[BenchmarkReturn],
    i.[PreviousUnitPosition],
    i.[SecurityId]		
FROM inserted i
WHERE NOT EXISTS(SELECT 1 FROM  [TradingData].[ExcessTimeSerie] T WHERE T.SecurityId = i.SecurityId and T.[Date] = i.[Date]  and T.[BenchmarkId] = i.[BenchmarkId])
END